﻿using Dapper;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace ExcelFileQueryTool
{
    public sealed class BillSalesService
    {
        public string ConnectionString { get; private set; }
        public BillSalesService(string connStr)
        {
            if (string.IsNullOrEmpty(connStr))
                throw new ArgumentNullException("connStr");

            ConnectionString = connStr;
        }

        public int Add(BillSales data)
        {
            if (data == null)
                return 0;

            using (SqlConnection conn = new SqlConnection(this.ConnectionString))
            {
                var sql = string.Format(@"INSERT INTO [dbo].[Bill_Sales]
           ([ClientId]
           ,[ClientName]
           ,[ShippingAddress]
           ,[SalesDate]
           ,[ProductId]
           ,[ProductName]
           ,[ProductUnit]
           ,[ProductAmount]
           ,[ProductPrice]
           ,[LoadingFee]
           ,[ShippingFee]
           ,[ShippingMethod]
           ,[TotalMoney]
           ,[Status]
           ,[BillNumber]
           ,[CreatedTime]
           ,[LastUpdatedTime]
           ,[Note]
           ,[IsPaidOff]
           ,[BillType])
     VALUES
           (@ClientId
           ,@ClientName
           ,@ShippingAddress
           ,@SalesDate
           ,@ProductId
           ,@ProductName
           ,@ProductUnit
           ,@ProductAmount
           ,@ProductPrice
           ,@LoadingFee
           ,@ShippingFee
           ,@ShippingMethod
           ,@TotalMoney
           ,@Status
           ,@BillNumber
           ,@CreatedTime
           ,@LastUpdatedTime
           ,@Note
           ,@IsPaidOff
           ,@BillType);
SELECT SCOPE_IDENTITY();");

                data.Id = SqlMapper.ExecuteScalar<int>(conn, sql, data);
                return data.Id.Value;
            }
        }

        public void Add(IEnumerable<BillSales> dataList)
        {
            if (dataList == null || dataList.Count() == 0)
                return;

            using (SqlConnection conn = new SqlConnection(this.ConnectionString))
            {
                var sql = string.Format(@"INSERT INTO [dbo].[Bill_Sales]
           ([ClientId]
           ,[ClientName]
           ,[ShippingAddress]
           ,[SalesDate]
           ,[ProductId]
           ,[ProductName]
           ,[ProductUnit]
           ,[ProductAmount]
           ,[ProductPrice]
           ,[LoadingFee]
           ,[ShippingFee]
           ,[ShippingMethod]
           ,[TotalMoney]
           ,[Status]
           ,[BillNumber]
           ,[CreatedTime]
           ,[LastUpdatedTime]
           ,[Note]
           ,[IsPaidOff]
           ,[BillType])
     VALUES
           (@ClientId
           ,@ClientName
           ,@ShippingAddress
           ,@SalesDate
           ,@ProductId
           ,@ProductName
           ,@ProductUnit
           ,@ProductAmount
           ,@ProductPrice
           ,@LoadingFee
           ,@ShippingFee
           ,@ShippingMethod
           ,@TotalMoney
           ,@Status
           ,@BillNumber
           ,@CreatedTime
           ,@LastUpdatedTime
           ,@Note
           ,@IsPaidOff
           ,@BillType)");

                SqlMapper.Execute(conn, sql, dataList);
            }
        }

        public int Update(BillSales data)
        {
            if (data == null)
                return 0;

            if (data.Id.HasValue == false)
                throw new ArgumentNullException("data.Id", "No value");

            using (SqlConnection conn = new SqlConnection(this.ConnectionString))
            {
                var sql = @"UPDATE [dbo].[Bill_Sales]
   SET [ClientId] = @ClientId
      ,[ClientName] = @ClientName
      ,[ShippingAddress] = @ShippingAddress
      ,[SalesDate] = @SalesDate
      ,[ProductId] = @ProductId
      ,[ProductName] = @ProductName
      ,[ProductUnit] = @ProductUnit
      ,[ProductAmount] = @ProductAmount
      ,[ProductPrice] = @ProductPrice
      ,[LoadingFee] = @LoadingFee
      ,[ShippingFee] = @ShippingFee
      ,[ShippingMethod] = @ShippingMethod
      ,[TotalMoney] = @TotalMoney
      ,[BillNumber] = @BillNumber
      ,[LastUpdatedTime] = @LastUpdatedTime
      ,[Note] = @Note
      ,[IsPaidOff] = @IsPaidOff
      ,[BillType] = @BillType
 WHERE Id=@Id";

                return SqlMapper.Execute(conn, sql, data);
            }
        }

        public BillSales GetById(int id)
        {
            using (SqlConnection conn = new SqlConnection(this.ConnectionString))
            {
                var sql = "select * from Bill_Sales where Id=" + id.ToString() + " and [Status]=" + (short)DBRecordStatus.Normal;
                return SqlMapper.Query<BillSales>(conn, sql).FirstOrDefault();
            }
        }

        public IEnumerable<BillSales> GetListByClientName(string clientName, DateTime? sales_date_begin = null, DateTime? sales_date_end = null)
        {
            if (string.IsNullOrEmpty(clientName))
                return null;

            var sql = string.Format("select * from Bill_Sales where [Status]={1} and ClientName='{0}'", clientName, (short)DBRecordStatus.Normal);
            if (sales_date_begin.HasValue)
            {
                sql += string.Format(" and SalesDate >= '{0}'", sales_date_begin.Value.ToString("yyyy-M-d"));
            }

            if (sales_date_end.HasValue)
            {
                sql += string.Format(" and SalesDate <= '{0}'", sales_date_end.Value.ToString("yyyy-M-d"));
            }

            using (SqlConnection conn = new SqlConnection(this.ConnectionString))
            {
                return SqlMapper.Query<BillSales>(conn, sql);
            }
        }

        public IEnumerable<BillSales> GetListByClientId(int clientId)
        {
            var sql = "select * from Bill_Sales where ClientId=" + clientId + " and [Status]=" + (short)DBRecordStatus.Normal;
            using (SqlConnection conn = new SqlConnection(this.ConnectionString))
            {
                return SqlMapper.Query<BillSales>(conn, sql);
            }
        }

        public void UpdateBillNumber(IEnumerable<KeyValuePair<int, string>> billNOList)
        {
            if (billNOList == null || billNOList.Count() == 0)
                return;

            using (SqlConnection conn = new SqlConnection(this.ConnectionString))
            {
                var sql = "update Bill_Sales set BillNumber=@Value where Id=@Key";
                SqlMapper.Execute(conn, sql, billNOList);
            }
        }

        public void UpdateBillNumber(int id, string billNO)
        {
            using (SqlConnection conn = new SqlConnection(this.ConnectionString))
            {
                var sql = string.Format("update Bill_Sales set BillNumber='{0}' where Id={1}", billNO, id.ToString());
                SqlMapper.Execute(conn, sql);
            }
        }


        public void UpdateClientNameById(int clientId, string newName)
        {
            using (SqlConnection conn = new SqlConnection(this.ConnectionString))
            {
                var sql = string.Format("update Bill_Sales set ClientName='{0}' where ClientId={1}", newName, clientId);
                SqlMapper.Execute(conn, sql);
            }
        }
        public void UpdateClientIdByName(string clientName, int newId)
        {
            using (SqlConnection conn = new SqlConnection(this.ConnectionString))
            {
                var sql = string.Format("update Bill_Sales set ClientId={0} where ClientName='{1}'", newId, clientName);
                SqlMapper.Execute(conn, sql);
            }
        }

        public int Delete(int id, bool fake_del = true)
        {
            using (SqlConnection conn = new SqlConnection(this.ConnectionString))
            {
                string sql;

                if (fake_del)
                    sql = string.Format("update Bill_Sales set [Status]=" + (short)DBRecordStatus.Deleted + " where [Id]=" + id.ToString());
                else
                    sql = string.Format("delete Bill_Sales where [Id]=" + id.ToString());

                return SqlMapper.Execute(conn, sql);
            }
        }
    }
}
